/**
 ****************************************************************************
 * Copyright (C) Marcelo F. Ochoa. All rights reserved.                      *
 * ------------------------------------------------------------------------- *
 * This software is published under the terms of the Apache Software License *
 * version 1.1, a copy of which has been included  with this distribution in *
 * the LICENSE file.                                                         *
 */

package com.prism.oracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.prism.ConnInfo;
import com.prism.SPProc;

/**
 * This class implements a ConcreteProduct class of abtract factory patterm.
 * Define a product object (SPProcLite represent a Java Stored Procedure) to be created by the corresponding concrete factory.
 * Implements the AbstractProduct Interface (SPProc)
 */
public class SPProcLite extends SPProc {
    Logger log = Logger.getLogger(SPProcLite.class);

    /**
     * Create a concrete SPProc (SPProcLite). Find the Stored Procedure in the table all_arguments to get public definitios
     * If there are public Stored Procedures adds this definition to the Hashtable
     * of the superclass, and stores all overloaded ocurrence of the same StoreProcedure
     */
    public SPProc create(ConnInfo conn, String procname, Connection sqlconn) throws SQLException {
        if (log.isDebugEnabled())
          log.debug(".create overload for: '"+procname+"'");
        String owner;
        SPProcLite plp = new SPProcLite();
        int firstdotindex = procname.indexOf('.');
        int dotindex = procname.lastIndexOf('.');
        if (firstdotindex != dotindex)
            // if the sintax is owner.pkg.proc, extracts owner from the url
                owner = procname.substring(0, firstdotindex).toUpperCase();
        else
            // if not, owner is the username of the connection
                owner = conn.usr.toUpperCase();
        String plpackage;
        String plprocedure;
        try {
            if (firstdotindex != dotindex)
                // if the sintax is owner.pkg.proc, extracts pkg from the url
                    plpackage = procname.substring(firstdotindex + 1, dotindex).toUpperCase();
            else
                // if not, pkg is the first part of the url
                    plpackage = procname.substring(0, dotindex).toUpperCase();
            plprocedure = procname.substring(dotindex + 1);
        } catch (IndexOutOfBoundsException e) { // anonymous procedure
            plprocedure = procname;
            plpackage = null;
        }
        PreparedStatement cs;
        if (plpackage == null) {
            // anonymous procedure
            cs = sqlconn.prepareStatement("SELECT argument_name, overload, data_type, type_owner, type_name, type_subname" +
                " FROM all_arguments WHERE " + " upper(owner) = upper(?) AND package_name IS NULL AND upper(object_name) = upper(?) " +
                " ORDER BY overload");
            cs.setString(1, owner);
            cs.setString(2, plprocedure);
        } else {
            // package.procedure
            cs = sqlconn.prepareStatement("SELECT argument_name, overload, data_type, type_owner, type_name, type_subname" +
                " FROM all_arguments WHERE " + " upper(owner) = upper(?) AND upper(package_name) = upper(?) AND upper(object_name) = upper(?) " +
                " ORDER BY overload");
            cs.setString(1, owner);
            cs.setString(2, plpackage);
            cs.setString(3, plprocedure);
        }
        ResultSet rs = cs.executeQuery();
        String old_overload = "something";
        while (rs.next()) {
            String argument_name = rs.getString(1);
            String overload = rs.getString(2);
            if (overload == null) { // There is no overloading
                overload = "1";
            }
            if (!old_overload.equals(overload)) {
                plp.addProcedure(overload);
                old_overload = overload;
            }
            // if procedure has no argument, empty row is returned
            if (argument_name == null)  {
              if (log.isDebugEnabled())
                log.debug("            overload: "+overload+" no argument");
               continue;
            }
            argument_name = argument_name.toLowerCase();
            String data_type = rs.getString(3);
            String type_owner = rs.getString(4);
            String type_name = rs.getString(5);
            String type_subname = rs.getString(6);
            if ("PL/SQL TABLE".equals(data_type)) { // argument is ARRAY variable
                rs.next();
                plp.add(overload, argument_name, type_owner + "." + type_name + "." + type_subname);
                if (log.isDebugEnabled())
                  log.debug("            overload: "+overload + " arg: "+argument_name + " data_type: " + data_type + " type_name: " + type_name + "." + type_subname);
            } else { // argument is SCALAR variable
                plp.add(overload, argument_name, data_type);
                if (log.isDebugEnabled())
                  log.debug("            overload: "+overload + " arg: "+argument_name + " data_type: " + data_type);
            }
        }
        rs.close(); //don't wait for garbage collector
        cs.close(); //don't wait for garbage collector
        return plp;
    }
}
